package com.wxtf.platform.main.surgery;

import java.io.File;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.FormatUtil;
import com.wxtf.platform.tools.XLSUtils;
import com.wxtf.platform.main.proprietaryInquires.Married;
/**
 * <li>Title: SurgreyRecodeAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.surgery</li>
 * <li>Description: 四术登记与随访 表-tbc_surgery_record</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Sep 11, 2012 10:38:47 AM</li>
 *
 * @author Wenping_Ma
 * @version 1.0.0.0
 *
 */
public class SurgreyRecordAction extends LogicBase implements CommonsCallProvider{

	private static final Log log = LogFactory.getLog(SurgreyRecordAction.class);
	
	public SurgreyRecordAction(String jsonArray){

	}

	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
			} else if("queryPage_sur".equals(this.getMethodName())) {//查看四术详细
				msg = this.select_sur(this.getLogicData());
			} else if("queryPage_sur_vr".equals(this.getMethodName())) {//列出随访人员
				msg = this.select_sur_vr(this.getLogicData());
			} else if("queryPage_sur_vrall".equals(this.getMethodName())) {//随访内容
				msg = this.select_sur_vrall(this.getLogicData());
			} else if("save".equals(this.getMethodName())) {//添加四术信息
				msg = this.insert(this.getLogicData());
			} else if("save_vr".equals(this.getMethodName())) {//添加随访信息
				msg = this.insert_sur_vr(this.getLogicData());
			} else if("expData".equals(this.getMethodName())) {//导出
				msg = this.expData(this.getLogicData());
			} else if("delete".equals(this.getMethodName())) {//导出
				msg = this.delete(this.getLogicData());
			} 
			else{
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg) || "null".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	@Override
	public Object insert(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbcsr_id=AccessDataBaseUtil.getPKUUID();
		String curTime = FormatUtil.getCurentDateTime();
		String userId=user.getUserId();

		if("".equals(jsonObject.getString("tbcsr_type"))){//空
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"请选择手术类型\"}";
		}
		
		StringBuffer buff=new StringBuffer();
		buff.append("insert into tbc_surgery_record (tbcsr_id,tbe_id,org_id,tbcsr_type,tbcsr_place,tbcsr_hospital,"
				+"tbcsr_doctor,tbcsr_time,tbcsr_description,tbcsr_memo,reserve1,reserve2,reserve3,reserve4,reserve5,"
				+"create_by,create_time,last_modify_by,last_modify_time) values('").append(tbcsr_id)
				.append("','").append(jsonObject.getString("tbe_id")).append("','").append(jsonObject.getString("org_id"))
				.append("','").append(jsonObject.getString("tbcsr_type")).append("','").append(jsonObject.getString("tbcsr_place"))
				.append("','").append(jsonObject.getString("tbcsr_hospital")).append("','").append(jsonObject.getString("tbcsr_doctor"))
				.append("','").append(jsonObject.getString("tbcsr_time")).append("','").append(jsonObject.getString("tbcsr_description"))
				.append("','").append(jsonObject.getString("tbcsr_memo")).append("','").append("0")
				.append("','").append(jsonObject.getString("reserve2")).append("','").append(jsonObject.getString("reserve3"))
				.append("','").append(jsonObject.getString("reserve4")).append("','").append(jsonObject.getString("reserve5"))
				.append("','").append(userId).append("','").append(curTime).append("','").append(userId).append("','")
				.append(curTime).append("')");
		StringBuffer buff_u=new StringBuffer();
		if("109-3".equals(jsonObject.getString("tbcsr_type"))){//结扎
			buff_u.append("update tb_employee set tbe_birth_control_measures='104-1',last_modify_by='")
			      .append(userId).append("',last_modify_time='").append(curTime).append("' where tbe_id='")
			      .append(jsonObject.getString("tbe_id")).append("'");
		}else if("109-1".equals(jsonObject.getString("tbcsr_type"))){//皮埋
			buff_u.append("update tb_employee set tbe_birth_control_measures='104-3',last_modify_by='")
		      .append(userId).append("',last_modify_time='").append(curTime).append("' where tbe_id='")
		      .append(jsonObject.getString("tbe_id")).append("'");
		}else if("109-2".equals(jsonObject.getString("tbcsr_type"))){//上环
			buff_u.append("update tb_employee set tbe_birth_control_measures='104-2',last_modify_by='")
		      .append(userId).append("',last_modify_time='").append(curTime).append("' where tbe_id='")
		      .append(jsonObject.getString("tbe_id")).append("'");
		}else{
			buff_u.append("update tb_employee set tbe_birth_control_measures='104-7',last_modify_by='")
		      .append(userId).append("',last_modify_time='").append(curTime).append("' where tbe_id='")
		      .append(jsonObject.getString("tbe_id")).append("'");
		}
		StringBuffer buff_ur=new StringBuffer();
		buff_ur.append("update tbc_surgery_record set reserve1='1' where tbe_id='").append(jsonObject.getString("tbe_id")).append("'");
		String[] sqls={String.valueOf(buff_ur),String.valueOf(buff),String.valueOf(buff_u)};
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"登记成功\"";
	}

	String[] fields = {
			"tbe_id","org_id","tbe_idcard","tbe_work_permit",
			"tbe_employment_forms","tbe_name","tbe_former_name","tbe_spell_code","tbe_sex","tbe_age","tbe_birthday","tbe_nation","tbe_native_palce",
			"tbe_birthplace","tbe_join_time","tbe_seniority","tbe_family_phone","tbe_mobile","tbe_address","tbe_marital_status","tbe_marital_condition",
			"tbe_married_time","tbe_marriage_certificate","tbe_political_status","tbe_qualification","tbe_graduate_college","tbe_major","tbe_work_area",
			"tbe_position","tbe_is_again_birth","tbe_is_barrenness","the_is_menopause","tbe_is_late_marriage","tbe_is_late_birth","tbe_status","tbe_ba_status",
			"tbe_birth_control_measures","tbe_es_time","tbe_is_es","tbe_es_reason","tbe_children_status","tbe_order","tbe_memo",
			"reserve1","reserve2","reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time"
	};
	/*
	 * 筛选符合四术登记的人员
	 * **/
	@Override
	public Object select(Object jsonArray) throws Exception {
    UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		//sql = " from tb_employee where org_id in (" + user.getCurSubOrgId() + ") and tbe_employment_forms <> '31' and tbe_marital_status in('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617')";
		sql=" from tb_employee t1 left join tb_family_base f on f.tbe_id=t1.tbe_id where t1.org_id in (" + user.getCurSubOrgId() + ") and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and ((t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') and "+Married.from20to49("t1.tbe_birthday")+"  and t1.tbe_sex='女' and t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617') and t1.the_is_menopause='0' and t1.tbe_is_barrenness='0' ) or ( t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') and  t1.tbe_sex='男' and  ("+Married.from20to49("f.tbfi_spouse_birthday")+" or f.tbfi_spouse_birthday is null) and t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617') and t1.tbe_is_barrenness='0'  ) ) ";
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			if("init".equals(types)) {// 初始查询
				
			} else if("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");//机构ID串
				String tbe_name = jsonObject.getString("tbe_name");//姓名
				String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证
				StringBuffer buff = new StringBuffer();
				if(StringUtils.isNotBlank(orgIds)){
					buff.append(" and t1.org_id in ("+user.getCurSubOrgIds(orgIds)+") ");
				}
				if(StringUtils.isNotBlank(tbe_name)){
					buff.append(" and t1.tbe_name like '%").append(tbe_name).append("%'");
				}
				if(StringUtils.isNotBlank(tbe_idcard)){
					buff.append(" and t1.tbe_idcard like '%").append(tbe_idcard).append("%'");
				}
				sql += buff.toString();
			} 
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname != null && sortname != null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		String querySql = "select t1.tbe_id,t1.org_id,t1.tbe_idcard,t1.tbe_work_permit,t1.tbe_employment_forms,t1.tbe_name,t1.tbe_former_name,t1.tbe_spell_code,t1.tbe_sex,t1.tbe_age,t1.tbe_birthday,t1.tbe_nation,t1.tbe_native_palce,t1.tbe_birthplace,t1.tbe_join_time,t1.tbe_seniority,t1.tbe_family_phone,t1.tbe_mobile,t1.tbe_address,t1.tbe_marital_status,t1.tbe_marital_condition,t1.tbe_married_time,t1.tbe_marriage_certificate,t1.tbe_political_status,t1.tbe_qualification,t1.tbe_graduate_college,t1.tbe_major,t1.tbe_work_area,t1.tbe_position,t1.tbe_is_again_birth,t1.tbe_is_barrenness,t1.the_is_menopause,t1.tbe_is_late_marriage,t1.tbe_is_late_birth,t1.tbe_status,t1.tbe_ba_status,t1.tbe_birth_control_measures,t1.tbe_es_time,t1.tbe_is_es,t1.tbe_es_reason,t1.tbe_children_status,t1.tbe_order,t1.tbe_memo,t1.reserve1,t1.reserve2,t1.reserve3,t1.reserve4,t1.reserve5,t1.create_by,t1.create_time,t1.last_modify_by,t1.last_modify_time " + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	

	/* 
	 * 四术登记的详细信息
	 */
	String[] file_sur = {
			"tbe_name","tbcsr_id", "tbe_id", "org_id", "tbcsr_type", "tbcsr_place", "tbcsr_hospital", "tbcsr_doctor",
			"tbcsr_time", "tbcsr_description", "tbcsr_memo", "reserve1", "reserve2", "reserve3", "reserve4",
			"reserve5", "create_by", "create_time", "last_modify_by", "last_modify_time"
	};
	public Object select_sur(Object jsonArray) throws Exception {
	    UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
	    JSONArray array = JSONArray.fromObject(jsonArray);
	    JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
	    String tbe_id=jsonObject.getString("tbe_id");
			if("0".equals(user.getUserId())){
				sql = " from tbc_surgery_record ts left join  tb_employee te on ts.tbe_id=te.tbe_id where ts.tbe_id='"+tbe_id+"' ";
			} else {
				sql = " from tbc_surgery_record ts left join  tb_employee te on ts.tbe_id=te.tbe_id where ts.org_id in (" + user.getCurSubOrgId() + ") and ts.tbe_id='"+tbe_id+"' ";
			}
			
			
			if(array.size() > 0) {
				
				String types = jsonObject.getString("types");
				if("init".equals(types)) {// 初始查询
					
				} else if("serch1".equals(types)) {// 条件查询
					
				} 
			}
			// 排序
			String sortname = this.getRequest().getParameter("sortname");
			String sortorder = this.getRequest().getParameter("sortorder");
			if(sortname != null && sortname != null) {
				sql += " order by ts." + sortname + " " + sortorder;
			}
			String countSql = "select count(*) " + sql;
			String querySql = "select te.tbe_name,ts.tbcsr_id,ts.tbe_id,ts.org_id,ts.tbcsr_type,ts.tbcsr_place,ts.tbcsr_hospital,ts.tbcsr_doctor,"
				             +"ts.tbcsr_time,ts.tbcsr_description,ts.tbcsr_memo,ts.reserve1,ts.reserve2,ts.reserve3,ts.reserve4,ts.reserve5,"
				             +"ts.create_by,ts.create_time,ts.last_modify_by,ts.last_modify_time" + sql;
			return AccessDataBaseUtil.query4SQLPagging(file_sur, countSql, querySql, this.getPage(), this.getPagesize(), null);
		}
	/* 
	 * 查询需要随访的人员
	 * 根据手术类型确认需要随访的人员
	 */
	String[] fields_vr = {
			"tbe_id","org_id","org_name","tbe_name","tbe_former_name","tbe_sex","tbe_age","tbe_birthday","tbe_nation","tbe_native_palce",
			"tbe_birthplace","tbe_join_time","tbe_seniority","tbe_family_phone","tbe_mobile","tbe_address","tbe_marital_status",
			"tbcsr_id","tbcsr_type","tbcsr_time","tbcsr_description","tbcsrvr_time","last_modify_by","caption","tbcsrvr_surgery_before",
			"tbcsrvr_surgery_after","tbcsrvr_emmenia","tbcsrvr_offer"
	};
	public Object select_sur_vr(Object jsonArray) throws Exception {
		 UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		 JSONArray array = JSONArray.fromObject(jsonArray);
		 JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			
				sql = " FROM(select   te.tbe_id,te.org_id,te.tbe_name,te.tbe_former_name,te.tbe_sex,te.tbe_age,te.tbe_birthday,"
					+"te.tbe_nation,te.tbe_native_palce,te.tbe_birthplace,te.tbe_join_time,te.tbe_seniority,te.tbe_family_phone,te.tbe_mobile,"
					+"te.tbe_address,te.reserve1 as org_name,te.tbe_marital_status,tr.tbcsr_id,tr.tbcsr_type,tr.tbcsr_time,tr.tbcsr_description from tb_employee te,"
					+" tbc_surgery_record tr where te.tbe_id=tr.tbe_id and te.tbe_status not in ('102-3','102-4','102-5'))"
					+" ts LEFT JOIN ( select * from tbc_surgery_record_vr t1,t_user t2 where t1.LAST_MODIFY_BY=t2.USER_ID) tv ON ts.tbcsr_id=tv.tbcsr_id where ts.org_id in (" + user.getCurSubOrgId() + ") ";
			
			
			if(array.size() > 0) {
				if("init".equals(types)) {// 初始查询
					
				} else if("serch1".equals(types)) {// 条件查询
					String orgIds = jsonObject.getString("orgIds");//机构ID串
					String tbe_name = jsonObject.getString("tbe_name");//姓名
					String tbcsr_type=jsonObject.getString("tbcsr_type");//手术类型
					String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证
					StringBuffer buff = new StringBuffer();
					if(StringUtils.isNotBlank(orgIds)){
						buff.append(" and ts.org_id in ("+user.getCurSubOrgIds(orgIds)+") ");
					}
					if(StringUtils.isNotBlank(tbcsr_type)){
						if("0".equals(tbcsr_type)){
							
						}else{
						  buff.append(" and ts.tbcsr_type ='").append(tbcsr_type).append("'");
						}
					}
					if(StringUtils.isNotBlank(tbe_name)){
						buff.append(" and ts.tbe_name like '%").append(tbe_name).append("%'");
					}
					if(StringUtils.isNotBlank(tbe_idcard)){
						buff.append(" and ts.tbe_idcard like '%").append(tbe_idcard).append("%'");
					}
				
					sql += buff.toString();
				} else if("serch2".equals(types)) {// 高级查询
					String where = jsonObject.getString("where");//机构ID串
					sql += where;
				}
			}
			// 排序
			String sortname = this.getRequest().getParameter("sortname");
			String sortorder = this.getRequest().getParameter("sortorder");
			if(sortname != null && sortname != null) {
				sql += " order by " + sortname + " " + sortorder;
			}
			String countSql = "select count(*) " + sql;
			String querySql = "select ts.tbe_id,ts.org_id,ts.org_name,ts.tbe_name,ts.tbe_former_name,ts.tbe_sex,"
				+"ts.tbe_age,ts.tbe_birthday,ts.tbe_nation,ts.tbe_native_palce,ts.tbe_birthplace,ts.tbe_join_time,"
				+"ts.tbe_seniority,ts.tbe_family_phone,ts.tbe_mobile,ts.tbe_address,ts.tbe_marital_status,ts.tbcsr_id,ts.tbcsr_type,"
				+"ts.tbcsr_time,ts.tbcsr_description,tv.tbcsrvr_time,tv.last_modify_by,tv.caption,tv.tbcsrvr_surgery_before,"
				+"tv.tbcsrvr_surgery_after,tv.tbcsrvr_emmenia,tv.tbcsrvr_offer" + sql+" order by tv.tbcsrvr_time ";
			return AccessDataBaseUtil.query4SQLPagging(fields_vr, countSql, querySql, this.getPage(), this.getPagesize(), null);
		}
	/* 
	 * 
	 * 随访内容
	 */
	String[] fields_vrall = {
			"tbcsrvr_id","tbcsr_id","tbe_id","tbcsrvr_time","tbcsrvr_surgery_before","tbcsrvr_surgery_after","tbcsrvr_emmenia","tbcsrvr_offer"
	};
	public Object select_sur_vrall(Object jsonArray) throws Exception {
		 UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		 JSONArray array = JSONArray.fromObject(jsonArray);
		 JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String tbcsr_id=jsonObject.getString("tbcsr_id");
			String tbe_id=jsonObject.getString("tbe_id");
			if("0".equals(user.getUserId())){
				sql = " from tbc_surgery_record ts,tbc_surgery_record_vr tv where ts.tbcsr_id=tv.tbcsr_id and tv.tbcsr_id='"+tbcsr_id+"' and tv.tbe_id='"+tbe_id+"' ";
			} else {
				sql = " from tbc_surgery_record ts,tbc_surgery_record_vr tv where ts.tbcsr_id=tv.tbcsr_id and ts.org_id in (" + user.getCurSubOrgId() + ") and tv.tbcsr_id='"+tbcsr_id+"' and tv.tbe_id='"+tbe_id+"' ";
			}
			
			
			// 排序
			String sortname = this.getRequest().getParameter("sortname");
			String sortorder = this.getRequest().getParameter("sortorder");
			if(sortname != null && sortname != null) {
				sql += " order by " + sortname + " " + sortorder;
			}
			String countSql = "select count(*) " + sql;
			String querySql = "select tv.tbcsrvr_id,tv.tbcsr_id,tv.tbe_id,tv.tbcsrvr_time,tv.tbcsrvr_surgery_before,tv.tbcsrvr_surgery_after,tv.tbcsrvr_emmenia,tv.tbcsrvr_offer" + sql;
			return AccessDataBaseUtil.query4SQLPagging(fields_vrall, countSql, querySql, this.getPage(), this.getPagesize(), null);
		}
	/*
	 * 随访信息登记
	 * **/
	  public Object insert_sur_vr(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbcsrvr_id=AccessDataBaseUtil.getPKUUID();
		String curTime = FormatUtil.getCurentDateTime();
		String userId=user.getUserId();
		StringBuffer buff_del=new StringBuffer();
		buff_del.append("delete from tbc_surgery_record_vr where tbcsr_id='").append(jsonObject.getString("tbcsr_id_vr"))
		       .append("' and tbe_id='").append(jsonObject.getString("tbe_id_vr")).append("'");
		StringBuffer buff=new StringBuffer();
		buff.append("insert into tbc_surgery_record_vr (tbcsrvr_id,tbcsr_id,tbe_id,tbcsrvr_time,tbcsrvr_surgery_before,tbcsrvr_surgery_after,"
				+"tbcsrvr_emmenia,tbcsrvr_offer,reserve1,reserve2,reserve3,reserve4,reserve5,create_by,create_time,last_modify_by,"
				+"last_modify_time) values('").append(tbcsrvr_id)
				.append("','").append(jsonObject.getString("tbcsr_id_vr")).append("','").append(jsonObject.getString("tbe_id_vr"))
				.append("','").append(jsonObject.getString("tbcsrvr_time")).append("','").append(jsonObject.getString("tbcsrvr_surgery_before"))
				.append("','").append(jsonObject.getString("tbcsrvr_surgery_after"))
				.append("','").append(jsonObject.getString("tbcsrvr_emmenia")).append("','").append(jsonObject.getString("tbcsrvr_offer"))
				.append("','").append(jsonObject.getString("reserve1")).append("','").append(jsonObject.getString("reserve2")).append("','")
				.append(jsonObject.getString("reserve3")).append("','").append(jsonObject.getString("reserve4")).append("','")
				.append(jsonObject.getString("reserve5")).append("','").append(userId).append("','").append(curTime).append("','")
				.append(userId).append("','").append(curTime).append("')");
		
		String[] sqls={String.valueOf(buff_del),String.valueOf(buff)};
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"随访完成\"";
	}
	  
	  /** 
		 * 描述：expData 四术随访导出
		 * @param jsonArray
		 * @return
		 * @throws Exception
		 * @author ma_wenping
		 */
		public Object expData(Object jsonArray) throws Exception {
			UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
			JSONArray array = JSONArray.fromObject(jsonArray);
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String orgIds = jsonObject.getString("orgIds");//机构ID串
			String tbcsr_type = jsonObject.getString("tbcsr_type");//手术类别
			String tbe_name = jsonObject.getString("tbe_name");//姓名
			String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证

			StringBuffer buff = new StringBuffer();
			buff.append("select ts.org_name,ts.tbe_name,ts.tbe_sex,")
				.append("(select tmgd_name from tm_general_dictionary where tmgd_id=ts.tbcsr_type),")
				.append("ts.tbcsr_time,");
			if("mysql".equals(user.getDbType())){
				buff.append("if(tv.tbcsrvr_time is null,'未随访',tv.tbcsrvr_time) as tbcsrvr_time,")
				    .append("if(tv.caption is null,'未随访',tv.caption) as caption,");
			} else if("oracle".equals(user.getDbType())) {
				buff.append("decode(tv.tbcsrvr_time,'','未随访',tv.tbcsrvr_time) as tbcsrvr_time,")
				    .append("decode(tv.caption,'','未随访',tv.caption) as caption,");
			}
			buff.append("ts.tbe_address,ts.marital_status")
				.append("  FROM( select te.tbe_id,te.org_id,te.tbe_name,te.tbe_former_name,te.tbe_sex,te.tbe_age,te.tbe_birthday,")
				.append("te.tbe_nation,te.tbe_native_palce,te.tbe_birthplace,te.tbe_join_time,te.tbe_seniority,te.tbe_family_phone,te.tbe_mobile,")
				.append("te.tbe_address,te.org_name,tg.tmgd_name as marital_status,te.tbcsr_id,te.tbcsr_type,te.tbcsr_time,te.tbcsr_description  from ")
				.append("(select t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_former_name,t1.tbe_sex,t1.tbe_age,t1.tbe_birthday,")
				.append("t1.tbe_nation,t1.tbe_native_palce,t1.tbe_birthplace,t1.tbe_join_time,t1.tbe_seniority,t1.tbe_family_phone,t1.tbe_mobile,")
				.append("t1.tbe_address,t1.reserve1 as org_name,t1.tbe_marital_status,t2.tbcsr_id,t2.tbcsr_type,t2.tbcsr_time,t2.tbcsr_description")
				.append(" from tb_employee t1, tbc_surgery_record t2 where t1.tbe_id=t2.tbe_id and t1.tbe_status not in ('102-3','102-4','102-5')) te,tm_general_dictionary tg where te.tbe_marital_status=tg.tmgd_id )")
				.append(" ts LEFT JOIN ( select * from tbc_surgery_record_vr t1,t_user t2 where t1.LAST_MODIFY_BY=t2.USER_ID) tv ON ts.tbcsr_id=tv.tbcsr_id ");
				
			
			
			if(StringUtils.isBlank(orgIds)){
				buff.append(" where ts.org_id in (").append(user.getCurSubOrgId()).append(")");
			} else {
				buff.append(" where ts.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
			}
			if(StringUtils.isNotBlank(tbcsr_type)) {
				if("0".equals(tbcsr_type)){
					
				}else{
				  buff.append(" and ts.tbcsr_type ='").append(tbcsr_type).append("'");
				}
			}
			if(StringUtils.isNotBlank(tbe_name)) {
				buff.append(" and ts.tbe_name like '%").append(tbe_name).append("%'");
			}
			if(StringUtils.isNotBlank(tbe_idcard)) {
				buff.append(" and ts.tbe_idcard like '%").append(tbe_idcard).append("%'");
			}
			log.info("==:"+buff.toString());
			List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);
			
			
			String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
			String model = url + "/model/employee_sur_vr.xls";
			String file = url + "/file/employee_sur_vr.xls";
			String rfile = "jsp/app/xls/file/employee_sur_vr.xls";
			File ffrom = new File(model);
			File fto = new File(file);
			Common.copyFile(ffrom, fto);
			
			if(list.size() > 0){
				XLSUtils.writeXls(list, fto, "四术及随访信息表", 1);//写xls内容
			} else {
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"导出结果集没有记录\"}";
			}
			
			return "\"" + rfile + "\"";
		}
		
	  
	@Override
	public Object update(Object jsonArray) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		String[] ids = id.split(",");
		String sql_1 = "delete from tbc_surgery_record where tbcsr_id = ?";
		String sql_2 = "delete from tbc_surgery_record_vr where tbcsr_id = ?";
		AccessDataBaseUtil.deleteByIds(sql_1, ids);
		AccessDataBaseUtil.deleteByIds(sql_2, ids);
		return "\"删除成功\"";
		
	}
	
	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request=request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	public Object onCreate(Object jsonArray) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		// TODO Auto-generated method stub
		return null;
	}

}
